lundi 14 mars 2016

SQL Managing Database copies at change of year

Microsoft SQL Server 2005.

I have quite a lot of experience with day to day writing of SQL scripts, but am less familiar with SQL Admin tasks.

I have inherited a couple of 3rd party systems that require databases to be copied at the end of financial year. These databases are named "data_1415","data_1516" etc. These all exist on the same server.

This all works fine. However at year end I will need to copy / rename the database and then alter all the views and stored procedures in this new version to access the new database name.

So .. is there a more efficient method of updating multiple views/stored procedures?

I also wondered if using synonyms might help, but cannot get these to work unless they point to a specific table, which defeats the object.

In an ideal world I would simply name the databases "This Year" and "Last Year" and avoid doing any changes, but the users want to retain the current naming as it avoids any confusion in selecting the correct database in the 3rd party software.

Aucun commentaire:

Enregistrer un commentaire